﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using UFC.DBUtility;
using System.Xml;
using UFC.Common;
using System.Collections;
/*
 * 军检工序推送至QMS处理逻辑
 * zhangrj 
 * 2022-12-20
 */
namespace MESQualityHistoryRecord
{
    public class SendSpecMilitaryCheckInfoToQms
    {
        private const string m_cRecordType = "QMS_SpecMilitaryCheck";
        DataTable dtCheckResult = new DataTable();//检测项信息
        DataTable dtMaterial = new DataTable();//集件信息
        DataTable dtFileList = new DataTable();//附件信息
        /// <summary>
        /// 推送军检工序到Qms
        /// </summary>
        public void RunSpecMilitaryCheckInfo()
        {
            DataTable dtMain = GetSpecMilitaryCheckInfo();//获取可推送的军检工序
            dtMain.Columns.Add("ToTeamDispatchTime");//派工时间
            dtMain.Columns.Add("EmployeeName");//工人
            //循环推送每个工序
            foreach (DataRow item in dtMain.Rows)
            {
                //获取该工序派工时间
                item["ToTeamDispatchTime"] = GetSpecDisTime(item["ContainerID"].ToString(), item["workflowstepID"].ToString());
                //获取该工序报工人
                item["EmployeeName"] = GetSpecWorkerName(item["ContainerID"].ToString(), item["workflowstepID"].ToString());
                //获取该工序的检测项信息
                dtCheckResult = GetSpecCheckResultInfo(item["ContainerID"].ToString(), item["workflowstepID"].ToString());
                //获取该工序的集件信息
                dtMaterial = GetSpecCollectMaterialInfo(item["ContainerID"].ToString(), item["workflowstepID"].ToString());
                //附件
                dtFileList = new DataTable();
                string sendXML = GetXMLForSpecMilitaryCheck(item);//推送的xml
                LogHelp.WriteLog("工序军检接口-推送XML:" + sendXML);
                //推送给QMS
                MesToQms.MesService MTQ = new MesToQms.MesService();//
                NewMesToQms.unifiedFunApi NewMTQ = new NewMesToQms.unifiedFunApi();
                string resXml = NewMTQ.mesToQmsAPI(sendXML); //获取返回的xml

                LogHelp.WriteLog("工序军检接口-返回XML:" + resXml);

                if (!string.IsNullOrWhiteSpace(resXml))
                {
                    XmlDocument myxml = new XmlDocument();
                    myxml.LoadXml(resXml);
                    string xml = myxml.SelectSingleNode("RespMsg/document").InnerText;
                    myxml.LoadXml(xml);
                    string success = myxml.SelectSingleNode("TETTYP/RTYPE").InnerText;//
                    string message = myxml.SelectSingleNode("TETTYP/RTMSG").InnerText;//

                    //记录日志表
                    Dictionary<string, string> recordPara = new Dictionary<string, string>() { 
                        {"ParentId",item["workflowstepID"].ToString()},{"RecordType",m_cRecordType},
                        {"ContainerID",item["ContainerID"].ToString()},{"ContainerName",item["ContainerName"].ToString()},
                        {"ProductName",item["ProductName"].ToString()},{"WorkflowStepName",item["WorkflowStepName"].ToString()},
                        {"SendXml",sendXML},{"ResXml",resXml}
                    };
                    if (success == "S")
                    {
                        recordPara.Add("IsSuccess", "0");//成功
                    }
                    else
                    {
                        recordPara.Add("IsSuccess", "1");//失败                    
                    }
                    OracleDAL.InserOtherSystemRecord(recordPara);
                    //插入推送记录
                    InsertArmyCheckSendToQMS(item);
                }
            }
        }

        /// <summary>
        /// 插入工序军检提交QMS记录
        /// </summary>
        private void InsertArmyCheckSendToQMS(DataRow dr)
        {
            //查询mes是否有军检提交
            string strSql = "select * from ArmyCheckSendToQMS ac WHERE ac.containername = '{0}' AND ac.mfgordername = '{1}' AND ac.specno = '{2}'";
            strSql = string.Format(strSql, dr["containername"].ToString(), dr["mfgordername"].ToString(), dr["specno"].ToString());
            if (OracleHelper.Query(strSql).Tables[0].Rows.Count==0) //如果没有提交记录，则插入
            {
                strSql = @"INSERT INTO ArmyCheckSendToQMS
(Armychecksendtoqmsid,Containerid,Containername,Mfgordername,Planstartdate,Planenddate,Productname,Workflowid,Specno,Workflowstepname,Passqty)
VALUES(";
                strSql += "'" + Guid.NewGuid().ToString() + "',";
                strSql += "'" + dr["containerid"].ToString() + "',";
                strSql += "'" + dr["containername"].ToString() + "',";
                strSql += "'" + dr["mfgordername"].ToString() + "',";
                strSql += "to_date('" + dr["PlanStartTime"].ToString() + "','yyyy-MM-dd HH24:mi:ss'),";
                strSql += "to_date('" + dr["PlanFinishTime"].ToString() + "','yyyy-MM-dd HH24:mi:ss'),";
                strSql += "'" + dr["productname"].ToString() + "',";
                strSql += "'" + dr["workflowid"].ToString() + "',";
                strSql += "'" + dr["specno"].ToString() + "',";
                strSql += "'" + dr["workflowstepname"].ToString() + "',";
                strSql += "'" + dr["CheckQualifiedQty"].ToString() + "')";
                OracleHelper.ExecuteSql(strSql);
            } 
        }

        /// <summary>
        /// 获取工序的报工人
        /// </summary>
        /// <param name="containerID"></param>
        /// <param name="workflowstepID"></param>
        /// <returns></returns>
        private string GetSpecWorkerName(string containerID, string workflowstepID)
        {
            string sql = @"
SELECT wm_concat(t.employeename) EmployeeName FROM (
SELECT Distinct e.employeename FROM containerstepworkreport csw 
LEFT JOIN employee e ON e.employeeid = csw.workerid
WHERE csw.containerid = '{0}' 
AND csw.workflowstepid = '{1}' 
AND csw.isuse = 0 )t";
            sql = string.Format(sql, containerID, workflowstepID);
            DataTable dt = OracleHelper.QueryTable(sql,null);
            if (dt.Rows.Count>0)
            {
                return dt.Rows[0]["EmployeeName"].ToString();
            }
            return "";
        }

        /// <summary>
        /// 获取工序的派工时间
        /// </summary>
        /// <param name="containerID"></param>
        /// <param name="workflowstepID"></param>
        /// <returns></returns>
        private string GetSpecDisTime(string containerID, string workflowstepID)
        {
            string sql = @"
SELECT to_char(csd.createdate,'yyyy-MM-dd hh24:mi:ss') createdate FROM containerstepdispatch csd
WHERE csd.containerid = '{0}' AND csd.workflowstepid = '{1}' AND csd.isuse = 0
ORDER BY csd.createdate ASC";
            sql = string.Format(sql, containerID, workflowstepID);
            DataTable dt = OracleHelper.QueryTable(sql,null);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["createdate"].ToString();
            }
            return "";
        }

        /// <summary>
        /// 设置可推送的军检工序数据
        /// </summary>
        /// <returns></returns>
        private DataTable GetSpecMilitaryCheckInfo()
        {
            string sql = @"
SELECT mo.mfgordername,c.containerid,c.containername,pb.productname,p.DESCRIPTION ProductDesc,replace(cci.containername,pb.productname||'/') ProductBatchNo,
p.materialtrademark,u.attributevalue ZJYBH,ws.workflowstepid,ws.workflowstepname,s.specno,s.opdesc SpecDesc,
ck.acceptanceqty CheckQualifiedQty,to_char(c.plannedstartdate,'yyyy-MM-dd hh24:mi:ss') PlanStartTime,to_char(c.plannedcompletiondate,'yyyy-MM-dd hh24:mi:ss') PlanFinishTime,w.workflowid,
'' StartTime,'' FinishTime,'' EngineNo,'' VendorName,'WF-'||w.process_no||':'||w.workflowrevision WorkflowBaseName,u2.ATTRIBUTEVALUE affiliatedFactory,f.factoryName
FROM ( 
SELECT t.containerid,t.workflowstepid,SUM(t.acceptanceqty) acceptanceqty FROM(
SELECT cc.containerid ,cc.workflowstepid,cc.acceptanceqty
FROM containercheckinfo cc WHERE cc.isarmycheck = 1 AND cc.isuse = 0 
UNION ALL 
SELECT cf.containerid ,cf.workflowstepid,cf.acceptanceqty
FROM containerfinishedcheckinfo cf WHERE cf.isarmycheck = 1 AND cf.isuse = 0
)t GROUP BY t.containerid,t.workflowstepid
) ck
LEFT JOIN container c ON c.containerid = ck.containerid
LEFT JOIN containercurrentinfo cci ON cci.containerid = c.containerid
LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid
LEFT JOIN product p ON p.productid = c.productid
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
LEFT JOIN workflow w ON w.workflowid = c.mfgworkflowid
LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = ck.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN WritetoOtherSystem wos ON wos.containerid = c.containerid AND wos.parentid = ws.workflowstepid AND wos.recordtype = 'QMS_SpecMilitaryCheck'
LEFT JOIN Userattribute u ON u.parentid = c.containerid AND u.userattributename = '检验编号'
LEFT JOIN USERATTRIBUTE u2 ON u2.PARENTID =cci.FACTORYID AND u2.USERATTRIBUTENAME ='分厂代码'
LEFT JOIN Factory f on f.factoryid=cci.FACTORYID
WHERE wos.writetoothersystemid IS NULL AND c.status =1";
            return OracleHelper.QueryTable(sql,null);
        }

        /// <summary>
        /// 获取工序的检测项结果
        /// </summary>
        /// <param name="containerID"></param>
        /// <param name="workflowstepID"></param>
        /// <returns></returns>
        private DataTable GetSpecCheckResultInfo(string containerID, string workflowstepID)
        {
            string sql = @"
select st.checkno,st.name CheckPointName,st.checkpointnotedis CheckPointNote,st.drawingrequiredis DrawingRequire,st.checktype,
st.meopcheck,st.keycheck,st.qualitycheck,sr.checkpointresultnotedis CheckPointResultNote,e.fullname CheckerName,to_char(sr.checkdate,'yyyy-mm-dd hh24:mi:ss') checkdate,
'WF-'||w.process_no||':'||w.workflowrevision WorkflowBaseName,
sr.workcheckpointresultnotedis WorkCheckPointResultNote,ee.fullname WorkerName,to_char(sr.createdate,'yyyy-mm-dd hh24:mi:ss') WorkerCheckDate
from stepcheckpointresult sr
left join stepcheckpoint st on st.stepcheckpointid=sr.stepcheckpointid
left join workflowstep ws on ws.workflowstepid=st.workflowstepid
left join employee e on e.employeeid=sr.checkerid
LEFT JOIN employee ee ON ee.employeeid = sr.workerid
left join workflow w on w.workflowid=ws.workflowid
where sr.containerid='{0}' and ws.workflowstepid='{1}'";
            return OracleHelper.QueryTable(string.Format(sql, containerID, workflowstepID),null);
        }

        /// <summary>
        /// 获取工序的集件信息
        /// </summary>
        /// <param name="containerID"></param>
        /// <param name="workflowstepID"></param>
        /// <returns></returns>
        private DataTable GetSpecCollectMaterialInfo(string containerID, string workflowstepID)
        {
            string sql = @"
select cci.mfgordername,cci.containername,cci.productname,cci.realcontainerno ProductBatchNo
,cci.workflowname||':'||cci.workflowrevision WorkflowBaseName,cl.productname ChildProductName,p.description ChildProductdesc,cl.productbatchno ChildProductContainerName,
decode(pi.parttype,0,'大件',1,'小件','其他') ProductType,il.issueqty Qty,e.fullname ConfirmEmpoyeeName,
to_char(il.createdate,'yyyy-mm-dd hh24:mi:ss') ConfirmDate,il.issueerpmaterialid 
from issueerpmaterial il 
left join enginepartplan ep on ep.containerid=il.containerid
left join containercurrentinfo cci on cci.containerid=il.containerid
left join workflowstep ws on ws.workflowstepid=il.workflowstepid
left join collecterpmaterial cl on cl.collecterpmaterialid=il.collecterpmaterialid
left join partmenuinfo pi on pi.partmenuinfoid=il.partmenuinfoid
left join productbase pb on pb.productname=cl.productname
left join product p on p.productid=pb.revofrcdid
left join employee e on e.employeeid=il.employeeid
where il.containerid='{0}' AND il.workflowstepid = '{1}'";
            return OracleHelper.QueryTable(string.Format(sql, containerID, workflowstepID),null);
        }

        /// <summary>
        /// 获取军检工序推送的XML字符串
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string GetXMLForSpecMilitaryCheck(DataRow item)
        {
            XmlDocument doc = new XmlDocument();
            doc.AppendChild(doc.CreateXmlDeclaration("1.0", "utf-8", ""));
            XmlNode ReqNode = doc.CreateElement("ReqMsg");
            XmlNode headerNode = doc.CreateElement("header");
            XmlNode senderNode = doc.CreateElement("sendSysName");
            senderNode.InnerText = "APP_MES";
            XmlNode rcvNode = doc.CreateElement("rcvSysName");
            rcvNode.InnerText = "APP_QMS";
            XmlNode extNode = doc.CreateElement("extConfig");
            extNode.AppendChild(doc.CreateElement("isReTry"));
            extNode.LastChild.InnerText = "false";
            extNode.AppendChild(doc.CreateElement("FunName"));
            extNode.LastChild.InnerText = "MES_Send_Service";
            headerNode.AppendChild(senderNode);
            headerNode.AppendChild(rcvNode);
            headerNode.AppendChild(extNode);
            ReqNode.AppendChild(headerNode);
            XmlNode documentNode = doc.CreateElement("document");

            XmlNode root = doc.CreateElement("root");
            XmlNode qmsFun = doc.CreateElement("qmsFunName");
            qmsFun.InnerText = "MES_Send_SpecMilitaryCheckInfo";//方法名
            root.AppendChild(qmsFun);
            XmlNode qmsDocument = doc.CreateElement("qmsDocument");//具体业务内容

            string[] mainHeaderText = { "mfgOrderName",//订单号
                                        "containerName",//任务单号（批次号）
                                        "engineNo", //发动机号
                                        "productName", //零件件号
                                        "productDesc",//件号名称 add by longb 2023-04-20
                                        "productBatchNo",//零件批次
                                        "materialTrademark",//材料牌号
                                        "ZJYBH",//检验编号
                                        "vendorName",//原材料厂家
                                        "workflowBaseName",//工艺路线及版本
                                        "specNo",//工序号
                                        "specDesc",//工序名称
                                        "checkQualifiedQty",//合格数量
                                        "planStartTime",//计划开始时间
                                        "planFinishTime",//计划完成时间
                                        "toTeamDispatchTime",//派工时间
                                        "startTime",//实际开始时间
                                        "finishTime",//实际完成时间
                                        "employeeName",//工人
                                        "affiliatedFactory",//分厂代码 add by longb 2023-04-20
                                        "factoryName" //分厂名称 add by longb 2023-04-20
                                      };
            foreach (string ht in mainHeaderText)
            {
                XmlNode mainData = doc.CreateElement(ht);
                mainData.InnerText = item[ht].ToString();
                qmsDocument.AppendChild(mainData);
            }
            //追加检测项
            qmsDocument.AppendChild(GetCheckResultXml(doc));
            //追加集件信息
            qmsDocument.AppendChild(GetCollectMaterialXml(doc));
            //追加附件
            qmsDocument.AppendChild(GetDocmentXml(doc));

            root.AppendChild(qmsDocument);
            string strRoot = root.OuterXml.Replace("]]>", "]]]]><![CDATA[>");//cdata嵌套，特殊处理
            documentNode.InnerXml = "<![CDATA[" + strRoot + "]]>";
         
            var xml = documentNode.InnerXml;
            ReqNode.AppendChild(documentNode);
            doc.AppendChild(ReqNode);
            return doc.InnerXml;
        }
        /// <summary>
        /// 获取检测项xml
        /// </summary>
        /// <returns></returns>
        private XmlNode GetCheckResultXml(XmlDocument doc)
        {
            XmlNode checkItemList = doc.CreateElement("checkResultList");
            foreach (DataRow dr in dtCheckResult.Rows)
            {
                XmlNode checkItem = doc.CreateElement("checkResult");

                XmlNode node = doc.CreateElement("checkNo");//顺序号
                node.InnerText = dr["CheckNO"].ToString();
                checkItem.AppendChild(node);

                node = doc.CreateElement("checkPointName");//检测项名称
                node.InnerText = dr["CheckPointName"].ToString();
                checkItem.AppendChild(node);

                node = doc.CreateElement("checkPointNote");//检测项内容（有特殊符号需要用cdata）
                node.AppendChild(doc.CreateCDataSection(dr["CheckPointNote"].ToString()));
                checkItem.AppendChild(node);

                node = doc.CreateElement("drawingRequire");//图纸要求（有特殊符号需要用cdata）
                node.AppendChild(doc.CreateCDataSection(dr["DrawingRequire"].ToString()));
                checkItem.AppendChild(node);

                node = doc.CreateElement("workflowBaseName");//工艺路线及版本
                node.InnerText = dr["WorkflowBaseName"].ToString();
                checkItem.AppendChild(node);

                node = doc.CreateElement("checkType");//检测类型
                node.InnerText = dr["CheckType"].ToString();
                checkItem.AppendChild(node);

                node = doc.CreateElement("keyCheck");//关键过程检
                node.InnerText = dr["KeyCheck"].ToString();
                checkItem.AppendChild(node);

                node = doc.CreateElement("meopCheck");//工序检
                node.InnerText = dr["MeopCheck"].ToString();
                checkItem.AppendChild(node);

                node = doc.CreateElement("qualityCheck");//质保单检
                node.InnerText = dr["QualityCheck"].ToString();
                checkItem.AppendChild(node);

                node = doc.CreateElement("checkPointResultNote");//检验员实测值（有特殊符号需要用cdata）
                node.AppendChild(doc.CreateCDataSection(dr["CheckPointResultNote"].ToString()));
                checkItem.AppendChild(node);

                node = doc.CreateElement("checkerName");//检验员
                node.InnerText = dr["CheckerName"].ToString();
                checkItem.AppendChild(node);

                node = doc.CreateElement("checkDate");//检验录入时间
                node.InnerText = dr["CheckDate"].ToString();
                checkItem.AppendChild(node);

                node = doc.CreateElement("workCheckPointResultNote");//操作工实测值
                node.InnerText = dr["WorkCheckPointResultNote"].ToString();
                checkItem.AppendChild(node);

                node = doc.CreateElement("workerName");//操作工
                node.InnerText = dr["WorkerName"].ToString();
                checkItem.AppendChild(node);

                node = doc.CreateElement("workerCheckDate");//操作工录入时间
                node.InnerText = dr["WorkerCheckDate"].ToString();
                checkItem.AppendChild(node);

                checkItemList.AppendChild(checkItem);
            }
            return checkItemList;
        }
        /// <summary>
        /// 获取集件信息Xml
        /// </summary>
        /// <returns></returns>
        private XmlNode GetCollectMaterialXml(XmlDocument doc)
        {
            //追加集件信息
            XmlNode collectList = doc.CreateElement("collectMaterialList");
            foreach (DataRow dr in dtMaterial.Rows)
            {
                XmlNode collectItem = doc.CreateElement("collectMaterial");

                XmlNode node = doc.CreateElement("childProductName");
                node.InnerText = dr["ChildProductName"].ToString();
                collectItem.AppendChild(node);

                node = doc.CreateElement("childProductDesc");
                node.InnerText = dr["ChildProductDesc"].ToString();
                collectItem.AppendChild(node);

                node = doc.CreateElement("childProductContainerName");
                node.InnerText = dr["ChildProductContainerName"].ToString();
                collectItem.AppendChild(node);

                node = doc.CreateElement("productType");
                node.InnerText = dr["ProductType"].ToString();
                collectItem.AppendChild(node);

                node = doc.CreateElement("qty");
                node.InnerText = dr["Qty"].ToString();
                collectItem.AppendChild(node);

                node = doc.CreateElement("confirmEmpoyeeName");
                node.InnerText = dr["ConfirmEmpoyeeName"].ToString();
                collectItem.AppendChild(node);

                node = doc.CreateElement("confirmDate");
                node.InnerText = dr["ConfirmDate"].ToString();
                collectItem.AppendChild(node);

                collectList.AppendChild(collectItem);
            }
            return collectList;
        }
        /// <summary>
        /// 获取附件信息Xml
        /// </summary>
        /// <returns></returns>
        private XmlNode GetDocmentXml(XmlDocument doc)
        {
            //追加附件信息
            XmlNode documentList = doc.CreateElement("documentList");
            foreach (DataRow dr in dtFileList.Rows)
            {
                XmlNode fileItem = doc.CreateElement("document");


                XmlNode node = doc.CreateElement("fileID");
                node.InnerText = dr["FileID"].ToString();
                fileItem.AppendChild(node);

                node = doc.CreateElement("fileName");
                node.InnerText = dr["FileName"].ToString();
                fileItem.AppendChild(node);

                documentList.AppendChild(fileItem);
            }
            return documentList;
        }
    }
}
